package net.bw.realtime.jtp.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.bw.realtime.jtp.bean.ProvinceAmountBean;
import org.bw.realtime.jtp.bean.ProvinceAmountRankBean;
import org.bw.realtime.jtp.bean.ProvinceCntRankBean;

import java.util.List;

@Mapper
public interface TradeProvinceMapper {

//-- 各个省份下单金额
	@Select("SELECT\n" +
			"      province_id, province_name\n" +
			"     , count(order_id) AS order_count\n" +
			"     , sum(split_total_amount) AS order_amount\n" +
			"FROM jtp_mall_doris.dwd_order_detail\n" +
			"WHERE cur_date = #{date} \n" +
			"GROUP BY  cur_date,province_id, province_name")
	List<ProvinceAmountBean> selectProvinceAmount(@Param("date")String date);

//-- 各个省份下单量排名
	@Select("SELECT\n" +
			"    province_id, province_name\n" +
			"     , count(order_id) AS order_count\n" +
			"FROM jtp_mall_doris.dwd_order_detail\n" +
			"WHERE cur_date = #{date}\n" +
			"GROUP BY  cur_date,province_id, province_name\n" +
			"ORDER BY order_count DESC\n" +
			"limit 6")
	List<ProvinceCntRankBean> selectProvinceCntRank(@Param("date")String date);


//	-- 各个省份销售量

	@Select("SELECT\n" +
			"    sum(if(cur_date = \"2025-01-21\", split_total_amount, 0)) as one_month_amount,\n" +
			"    sum(if(cur_date = \"2025-06-01\", split_total_amount, 0)) as six_month_amount,\n" +
			"    province_id\n" +
			"     , province_name\n" +
			"     , sum(split_total_amount) AS order_amount\n" +
			"FROM jtp_mall_doris.dwd_order_detail\n" +
			"GROUP BY  province_id, province_name\n" +
			"ORDER BY one_month_amount DESC\n" +
			"limit 6")
	List<ProvinceAmountRankBean> selectProvinceAmountRank(@Param("date")String date);
}

